﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Data.SqlClient;
using System.Data;
using System.Xml.Linq;
using System.Web;

namespace PotbsData
{
    public class DataPump
    {
        private const string sqlConnString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=""C:\Documents and Settings\Khelben\My Documents\Visual Studio 2008\Projects\potbs-emsSolution\potbs-ems\App_Data\potbs-ems.mdf"";Integrated Security=True;Connect Timeout=30;User Instance=True";

        public static void PumpBulkServerStatus(XmlDataDocument xdata)
        {
            using(SqlConnection conn = new SqlConnection(sqlConnString))
            using (SqlCommand cmd = conn.CreateCommand())
            {
                //SqlParameterCollection test;
                //cmd.Parameters
                cmd.Parameters.Add("@updated", SqlDbType.DateTime);
                cmd.Parameters.Add("@name", SqlDbType.NVarChar, 50);
                cmd.Parameters.Add("@status", SqlDbType.NVarChar, 50);
                cmd.Parameters.Add("@version", SqlDbType.NVarChar, 50);
                cmd.Parameters.Add("@england", SqlDbType.TinyInt, 1);
                cmd.Parameters.Add("@spain", SqlDbType.TinyInt, 1);
                cmd.Parameters.Add("@france", SqlDbType.TinyInt, 1);
                cmd.Parameters.Add("@pirate", SqlDbType.TinyInt, 1);

                cmd.CommandText = "sp_PumpStatus";
                cmd.CommandType = CommandType.StoredProcedure;

                conn.Open();

                cmd.Parameters["@updated"].Value = xdata.DataSet.Tables["ServerList"].Rows[0]["updated"];
                foreach (DataRow row in xdata.DataSet.Tables["server"].Rows)
                {
                    cmd.Parameters["@name"].Value = row["name"];
                    cmd.Parameters["@status"].Value = row["status"];
                    cmd.Parameters["@version"].Value = row["version"];
                    cmd.Parameters["@england"].Value = row["ENGLAND"];
                    cmd.Parameters["@spain"].Value = row["SPAIN"];
                    cmd.Parameters["@france"].Value = row["FRANCE"];
                    cmd.Parameters["@pirate"].Value = row["PIRATE"];

                    cmd.ExecuteNonQuery();
                }
            }  
        }

        public static void LandmarkStatus(String XMLSchema)
        {
            using (SqlConnection conn = new SqlConnection(sqlConnString))
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_GetServerIDs";
                conn.Open();
                SqlDataReader Servers = cmd.ExecuteReader();
                if (Servers.HasRows)
                {
                    while (Servers.Read())
                    {
                        XmlDataDocument xdata = new XmlDataDocument();
                        xdata.DataSet.ReadXmlSchema(XMLSchema);
                        xdata.Load(XmlClient.GetLandmarkStatus(Servers["name"].ToString()).CreateReader());
                        PumpBulkLandmarkStatus(xdata, Servers["name"].ToString());
                        xdata = null;
                    }
                }
            }
        }

        private static void PumpBulkLandmarkStatus(XmlDataDocument xdata, string server)
        {
            using (SqlConnection conn = new SqlConnection(sqlConnString))
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.Parameters.Add("@updated", SqlDbType.DateTime, 8);
                cmd.Parameters.Add("@server", SqlDbType.NVarChar, 50);
                cmd.Parameters.Add("@name", SqlDbType.NVarChar, 50);
                cmd.Parameters.Add("@currOwner", SqlDbType.NVarChar, 50);
                cmd.Parameters.Add("@defaultOwner", SqlDbType.NVarChar, 50);
                cmd.Parameters.Add("@level", SqlDbType.NVarChar, 50);
                cmd.Parameters.Add("@xPosition", SqlDbType.Float);
                cmd.Parameters.Add("@zPosition", SqlDbType.Float);
                cmd.Parameters.Add("@area", SqlDbType.NVarChar, 50);
                cmd.Parameters.Add("@conquerable", SqlDbType.NVarChar, 50);
                cmd.Parameters.Add("@portState", SqlDbType.NVarChar, 50);
                cmd.Parameters.Add("@piratePvPRange", SqlDbType.Decimal, 18);
                cmd.Parameters.Add("@fullPvPRange", SqlDbType.Decimal, 18);
                cmd.Parameters.Add("@timeOfLastUnrestChange", SqlDbType.NVarChar, 50);
                cmd.Parameters.Add("@englandUnrestPoints", SqlDbType.Int);
                cmd.Parameters.Add("@spainUnrestPoints", SqlDbType.Int);
                cmd.Parameters.Add("@franceUnrestPoints", SqlDbType.Int);
                cmd.Parameters.Add("@pirateUnrestPoints", SqlDbType.Int);
                cmd.Parameters.Add("@protectionStateEndTime", SqlDbType.NVarChar, 50);
                cmd.Parameters.Add("@attackerBattlePoints", SqlDbType.Int);
                cmd.Parameters.Add("@ownerBattleConquest", SqlDbType.Int);
                cmd.Parameters.Add("@englandConquestPoints", SqlDbType.TinyInt);
                cmd.Parameters.Add("@spainConquestPoints", SqlDbType.TinyInt);
                cmd.Parameters.Add("@franceConquestPoints", SqlDbType.TinyInt);
                cmd.Parameters.Add("@pirateConquestPoints", SqlDbType.TinyInt);
                cmd.Parameters.Add("@englandConquerCount", SqlDbType.TinyInt);
                cmd.Parameters.Add("@spainConquerCount", SqlDbType.TinyInt);
                cmd.Parameters.Add("@franceConquerCount", SqlDbType.TinyInt);
                cmd.Parameters.Add("@pirateConquerCount", SqlDbType.TinyInt);
                cmd.Parameters.Add("@timeOfLastBattle", SqlDbType.NVarChar, 50);
                cmd.Parameters.Add("@unrestStateEndTime", SqlDbType.NVarChar, 50);
                cmd.Parameters.Add("@battleStartDate", SqlDbType.NVarChar, 50);
                cmd.Parameters.Add("@attacker", SqlDbType.NVarChar, 50);
                cmd.Parameters.Add("@defender", SqlDbType.NVarChar, 50);

                cmd.CommandText = "sp_PumpLandmarks";
                cmd.CommandType = CommandType.StoredProcedure;

                conn.Open();

                cmd.Parameters["@server"].Value = server;
                cmd.Parameters["@updated"].Value = xdata.DataSet.Tables["PortList"].Rows[0]["updated"];
                foreach (DataRow row in xdata.DataSet.Tables["Port"].Rows)
                {
                    cmd.Parameters["@name"].Value = row["name"];
                    cmd.Parameters["@currOwner"].Value = row["currOwner"];
                    cmd.Parameters["@defaultOwner"].Value = row["defaultOwner"];
                    cmd.Parameters["@level"].Value = row["level"];
                    cmd.Parameters["@xPosition"].Value = row["xPosition"];
                    cmd.Parameters["@zPosition"].Value = row["zPosition"];
                    cmd.Parameters["@area"].Value = row["area"];
                    cmd.Parameters["@conquerable"].Value = row["conquerable"];
                    cmd.Parameters["@portState"].Value = row["portState"];
                    cmd.Parameters["@piratePvPRange"].Value = row["piratePvPRange"];
                    cmd.Parameters["@fullPvPRange"].Value = row["fullPvPRange"];
                    cmd.Parameters["@timeOfLastUnrestChange"].Value = row["timeOfLastUnrestChange"];
                    cmd.Parameters["@englandUnrestPoints"].Value = row["englandUnrestPoints"];
                    cmd.Parameters["@spainUnrestPoints"].Value = row["spainUnrestPoints"];
                    cmd.Parameters["@franceUnrestPoints"].Value = row["franceUnrestPoints"];
                    cmd.Parameters["@pirateUnrestPoints"].Value = row["pirateUnrestPoints"];
                    cmd.Parameters["@protectionStateEndTime"].Value = row["protectionStateEndTime"];
                    cmd.Parameters["@attackerBattlePoints"].Value = row["attackerBattlePoints"];
                    cmd.Parameters["@ownerBattleConquest"].Value = row["ownerBattleConquest"];
                    cmd.Parameters["@englandConquestPoints"].Value = row["englandConquestPoints"];
                    cmd.Parameters["@spainConquestPoints"].Value = row["spainConquestPoints"];
                    cmd.Parameters["@franceConquestPoints"].Value = row["franceConquestPoints"];
                    cmd.Parameters["@pirateConquestPoints"].Value = row["pirateConquestPoints"];
                    cmd.Parameters["@englandConquerCount"].Value = row["englandConquerCount"];
                    cmd.Parameters["@spainConquerCount"].Value = row["spainConquerCount"];
                    cmd.Parameters["@franceConquerCount"].Value = row["franceConquerCount"];
                    cmd.Parameters["@pirateConquerCount"].Value = row["pirateConquerCount"];
                    cmd.Parameters["@timeOfLastBattle"].Value = row["timeOfLastBattle"];
                    cmd.Parameters["@unrestStateEndTime"].Value = row["unrestStateEndTime"];
                    cmd.Parameters["@battleStartDate"].Value = row["battleStartDate"];
                    cmd.Parameters["@attacker"].Value = row["attacker"];
                    cmd.Parameters["@defender"].Value = row["defender"];

                    cmd.ExecuteNonQuery();
                }
            }
        }

        public static void PumpStatics(XmlDataDocument xdata)
        {
            using (SqlConnection conn = new SqlConnection(sqlConnString))
            using (SqlCommand cmd = conn.CreateCommand())
            {
                DateTime dt = DateTime.Now;
                cmd.Parameters.Add("@id", SqlDbType.NVarChar, 50);
                cmd.Parameters.Add("@name", SqlDbType.NVarChar, 50);
                cmd.Parameters.Add("@desc", SqlDbType.NVarChar, -1);
                cmd.Parameters.Add("@updated", SqlDbType.DateTime);

                cmd.CommandText = "sp_PumpStatics";
                cmd.CommandType = CommandType.StoredProcedure;

                conn.Open();
                foreach (DataRow row in xdata.DataSet.Tables["string"].Rows)
                {
                    cmd.Parameters["@id"].Value = row["id"];
                    cmd.Parameters["@name"].Value = row["name"];
                    cmd.Parameters["@desc"].Value = row["desc"];
                    cmd.Parameters["@updated"].Value = dt;

                    cmd.ExecuteNonQuery();
                }
            }
        }

    }
}
